SQL Queries Based on Employees and Departments Tables

Employees Table

Departments Table

I. Fetch all columns from the employees table

 SELECT *  FROM employees;     

II. Fetch all columns from the departments table

 SELECT *  FROM departments;     

III. Fetch all employees who work in the IT department

 SELECT e.* FROM employees e JOIN departments d ON e.department = d.dept_name WHERE d.dept_name = 'IT';     

IV. Fetch employee names and salaries, ordered by salary in descending order

 SELECT emp_name, salary FROM employees ORDER BY salary DESC;     

V. Find the total number of employees, highest salary, lowest salary, and average salary

 SELECT COUNT(*) AS total_employees,        MAX(salary) AS highest_salary,        MIN(salary) AS lowest_salary,        AVG(salary) AS average_salary FROM employees;     

VI. Get the total number of employees in each department

 SELECT department AS dept_name, COUNT(emp_id) AS total_employees FROM employees GROUP BY department;     

VII. Fetch departments having more than 1 employee

 SELECT department AS dept_name, COUNT(emp_id) AS total_employees FROM employees GROUP BY department HAVING COUNT(emp_id) > 1;     

VIII. Fetch all employees whose names start with 'A'

 SELECT * FROM employees WHERE emp_name LIKE 'A%';     

IX. Fetch employees with salaries between 50,000 and 80,000

 SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;     

X. Fetch employees who joined after January 1, 2020

 SELECT * FROM employees WHERE doj > '2020-01-01';     

XI. Join employees and departments to display employee names with their department names

 SELECT e.emp_name, d.dept_name FROM employees e JOIN departments d ON e.department = d.dept_name;     

XII. Join employees and departments to display employee names, department names, and locations

 SELECT e.emp_name, d.dept_name, d.location FROM employees e JOIN departments d ON e.department = d.dept_name;     

XIII. Fetch employees and their department names where the department location is 'Bangalore'

 SELECT e.emp_name, d.dept_name FROM employees e JOIN departments d ON e.department = d.dept_name WHERE d.location = 'Bangalore';     

XIV. Get the email addresses of employees whose salary is greater than 60,000

 SELECT email FROM employees WHERE salary > 60000;     

XV. Count how many employees have joined in each year

 SELECT YEAR(STR_TO_DATE(doj, '%d %b %Y')) AS join_year, COUNT(*) AS total_employees FROM employees GROUP BY join_year ORDER BY join_year;